<html>
<head>
<script type="text/javascript">
	function Search() {
		document.getElementById("sectionid").required = true;
		document.getElementById("startdate").required = true;
		document.getElementById("enddate").required = true;
		document.getElementById("action").value = "getreport";
	}
</script>
</head>
<body>
	<%@ page language="java" import="java.sql.*"%>
	<%
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/cse132b","cse132blogin", "1234567890");
			if ( request.getParameter("sectionid") == null ) {
				Statement sections = conn.createStatement();
				ResultSet rsSections = sections.executeQuery("SELECT * FROM sections s LEFT JOIN classes cl ON (s.class_id = cl.class_id) LEFT JOIN courses co ON (cl.course_id = co.course_id) WHERE year = 2014");
				
	%>
<form action="report2b.jsp" method="post">
<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>	
	<table border="0">
		<tr>
			<th>Review Session Planner</th>
		</tr>
		<tr>
			<th>Section ID</th>
			<th>Period Start Date</th>
			<th>Period End Date</th>
		</tr>
		<tr>
			<input type="hidden" id="action" name="action"/>
			<td>
				<select id="sectionid" name="sectionid">
					<%
					while (rsSections.next()) {
					%>
					<option value="<%=rsSections.getInt("section_id") %>"><%=rsSections.getString("department_abbreviation") %> <%=rsSections.getString("course_number") %> Section <%=rsSections.getInt("section_id") %></option>
					<%
					}
					%>
				</select>
			</td>
			<td>
				<input type="date" id="startdate" name="startdate"/>
			</td>
			<td>
				<input type="date" id="enddate" name="enddate"/>
			</td>
			<td>
				<input type="submit" onclick="Search()"/>
			</td>
		</tr>

	</table>
	</td>
</tr>
</table>
</form>	
	<% 
			} else {
			Statement range = conn.createStatement();
			range.execute("SELECT * FROM make_date_interval('" +request.getParameter("startdate") + "','" +request.getParameter("enddate") + "')");
			PreparedStatement report = conn.prepareStatement("SELECT * FROM potential_review_session_slots p1 INNER JOIN temp_date_intervals d ON (d.interval_day = p1.day) WHERE p1.slot_id NOT IN (SELECT p.slot_id FROM potential_review_session_slots p WHERE EXISTS (SELECT * FROM meetings LEFT JOIN sections on (meetings.section_id = sections.section_id) LEFT JOIN enroll ON (enroll.section_id = sections.section_id) LEFT JOIN classes ON (classes.class_id = sections.class_id) WHERE classes.quarter = (SELECT quarter FROM sections s INNER JOIN classes c ON (c.class_id = s.class_id) AND s.section_id = ?) AND classes.year = (SELECT year FROM sections s INNER JOIN classes c ON (c.class_id = s.class_id) AND s.section_id = ?) and enroll.student_id IN (SELECT student_id from enroll WHERE section_id = ?) AND p.day = meetings.meeting_day AND ((p.start_time >= meetings.meeting_start_time AND p.start_time < meetings.meeting_end_time) OR (p.end_time > meetings.meeting_start_time AND p.end_time <= meetings.meeting_end_time)))) ORDER BY interval_date, start_time ASC");
			report.setInt(1, Integer.parseInt(request.getParameter("sectionid")));
			report.setInt(2, Integer.parseInt(request.getParameter("sectionid")));
			report.setInt(3, Integer.parseInt(request.getParameter("sectionid")));
			ResultSet rsReport = report.executeQuery();
	%>
<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>	
	<table border="0">
		<tr>
			<th>Review Session Planner</th>
		</tr>
		<tr>
			<%
			while (rsReport.next()) {
			%>
			<%=rsReport.getString("day") %> <%=rsReport.getDate("interval_date") %> <%=rsReport.getTime("start_time") %> - <%=rsReport.getTime("end_time") %><br/>
			<% } %>
		</tr>
	</table>
	</td>
</tr>
</table>
</form>	
	<%
			}
		} catch (SQLException ex) {
			out.print("SQLException: " + ex.getMessage() + "<br/>");
			out.print("SQLState: " + ex.getSQLState() + "<br/>");
			out.print("VendorError: " + ex.getErrorCode() + "<br/>");
		}
	%>

</td>
</tr>
</table>
</body>
</html>